package model.handler.databaseHandling;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import model.Configuration;
import model.Utility;
import model.data.interfaces.IFinishable;
import model.data.tableModel.TableControl;
import model.data.tableModel.person.Customer;
import model.data.tableModel.person.Person;
import model.data.tableModel.person.Staff;
import model.data.tableModel.sale.Bill;
import model.data.tableModel.sale.Product;
import model.dataStructures.IDualMap;
import model.handler.fileHandling.FileHandler;
import model.invoicing.DateHandler;
import model.invoicing.DateHandler.InvoiceDate;
import model.invoicing.InvoiceHandler;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import enums.PaymentType;
import enums.Property;
import enums.Status;
import view.GUI.mainView.KindVBox.BoxType;

/**
 * Central utility class for interaction with the database.
 * <p>
 * Performs all SQL statements. As this class is a utility class it musn't be
 * invoked
 * 
 * @version %I%, %G%
 */
public final class QueryFactory {

	public enum QueryProperty {

		BALANCEINVOICE("balanceInvoice"), SALARYINVOICE("salaryInvoice");

		/**
		 * name as String for a Customer
		 */
		private final String name;

		/**
		 * Map of all existing Customers
		 */
		private static final Map<String, QueryProperty> stringToQueryProperty = new HashMap<>();

		static {
			for (QueryProperty f : QueryProperty.values()) {
				stringToQueryProperty.put(f.toString(), f);
			}
		}

		private QueryProperty(String name) {
			this.name = name;
		}

		@Override
		public String toString() {
			return name;
		}

		public static QueryProperty fromString(String s) {
			return QueryProperty.stringToQueryProperty.get(s);
		}
	}

	private final static Logger log = LogManager.getLogger("ERROR");
	private final static Logger debug = LogManager.getLogger("DEBUG");

	/**
	 * Mustn't be invoked because this class contains only static methods.
	 */
	private QueryFactory() {
		throw new AssertionError("The utility class StatementFactory musn't be invoked!");
	}

	/**
	 * Returns the file of an invoice of the given billID
	 * 
	 * @param dateOfInvoiceToSend
	 * @param billID
	 * @return
	 */
	public static File getInvoiceFile(String dateOfInvoiceToSend, int billID) {
		String query = "SELECT pdf FROM bill WHERE billID = ?;";
		File file = null;
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(query);) {
			file = new File(Utility.concatStrings(Configuration.PATHTEXINVOICECUSTOMER, String.valueOf(billID), "_", dateOfInvoiceToSend, ".pdf"));
			file.deleteOnExit();
			prep.setInt(1, billID);

			ResultSet result = prep.executeQuery();
			if (result.next()) {
				byte[] arr = result.getBytes("pdf");

				FileOutputStream fos = new FileOutputStream(file);
				fos.write(arr);
				fos.flush();
				fos.close();
			}

			debug.debug("Got invoice file for billID: " + billID);
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return file;
	}

	/**
	 * Gets the invoice defined by prop on the given date.
	 * 
	 * @param prop
	 * @param dateOfInvoiceToSend date of the invoice in the form of yyyy-MM
	 * @return File that should be read from the database or null if an error
	 *         occured/the file didn't exist.
	 */
	public static File getInvoice(QueryProperty prop, String dateOfInvoiceToSend) {
		StringBuilder sb = new StringBuilder();

		sb.append("SELECT ");
		sb.append(prop.toString());
		sb.append(" FROM invoice WHERE date = ?;");

		File file = null;
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(sb.toString());) {
			file = new File(Utility.concatStrings(Configuration.PATHTEXINVOICECUSTOMER, prop.toString(), dateOfInvoiceToSend, ".pdf"));
			file.deleteOnExit();
			prep.setString(1, dateOfInvoiceToSend);

			ResultSet result = prep.executeQuery();
			if (result.next()) {
				byte[] arr = result.getBytes(prop.toString());

				FileOutputStream fos = new FileOutputStream(file);
				fos.write(arr);
				fos.close();
			}
			
			debug.debug("Got " + prop.toString()+ " file for date: " + dateOfInvoiceToSend);
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return file;
	}

	/**
	 * Returns a sorted list of names of the products of a specific type.
	 * 
	 * @param type Type of the products which should be returned
	 * @return list of product names with the specific type
	 * @see BoxType
	 */
	public static List<String> getProducts(BoxType type) {
		List<String> list = new ArrayList<>();
		String str = "SELECT name FROM product WHERE status = 1 AND category = ?;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str);) {
			s.setString(1, type.toString());

			ResultSet rs = s.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("name"));
			}
			rs.close();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		Collections.sort(list);
		debug.debug("List of products of type: " + type.toString() + " was fetched.");
		return list;
	}

	/**
	 * @return the total salary of the given month
	 */
	public static double getTotalSalary(Date date) {
		Map<Staff, Integer> workingStaff = QueryFactory.getStaffWorking(DateHandler.getDayFromDate(date, InvoiceDate.FIRSTDAY),
				DateHandler.getDayFromDate(date, InvoiceDate.LASTDAY));
		int salesdayCount = 0;
		for (int i : workingStaff.values()) {
			salesdayCount += i;
		}
		double totalSalary = salesdayCount * Double.valueOf(QueryFactory.getProperty(Property.SALARY));
		debug.debug("Got total salary for date " + date.toString());
		return totalSalary;
	}

	/**
	 * @param the profit before the operator salary is deducted
	 * @return the salary of the operator
	 */
	public static double getSalaryOperator(double beforeProfit) {
		double profitSharing = Double.valueOf(QueryFactory.getProperty(Property.PROFITSHARING));
		double operatorSalary = Double.valueOf(QueryFactory.getProperty(Property.OPERATORSALARY));

		debug.debug("Got salary of operator");
		return beforeProfit * profitSharing + operatorSalary;
	}

	/**
	 * @param beforeProfit the profit before the operator salary is deducted
	 * @return the optional salary of the operator
	 */
	public static double getOptionalSalaryOperator(double beforeProfit) {
		double optProfitSharing = Double.valueOf(QueryFactory.getProperty(Property.OPTIONALPROFITSHARING));

		debug.debug("Got optional salary of operator");
		return beforeProfit * optProfitSharing;
	}

	/**
	 * Gets a list of all active customer names.
	 * 
	 * @return list of customer names
	 */
	public static List<String> getCustomer() {
		List<String> list = new ArrayList<>();
		String str = "SELECT name FROM customer WHERE customerStatus = 1;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str);) {
			ResultSet rs = s.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("name"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		debug.debug("List of customer names was fetched.");
		return list;
	}

	/**
	 * Returns a list of purchaseIDs of a given billID.
	 * 
	 * @param billID
	 * @return list with purchase IDs
	 */
	public static List<Integer> getPurchaseIDs(int billID) {

		String str = "SELECT purchaseID FROM purchase WHERE billID = ?;";
		List<Integer> res = new ArrayList<>();

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str)) {
			s.setInt(1, billID);
			ResultSet rs = s.executeQuery();

			while (rs.next()) {
				res.add(rs.getInt("purchaseID"));
			}
			rs.close();
			debug.debug("Got purchase IDs for billID: " + billID);
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return res;
	}

	/**
	 * Returns the billID of a customer on a specific date.
	 * 
	 * @param customerID ID of the customer
	 * @param date date of the bill (format: yyyy-MM)
	 * @return billID | -1 if no billID was found
	 */
	public static int getBillID(int customerID, String date) {

		String str = "SELECT billID FROM bill WHERE customerID = ? and date = ?;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str)) {
			s.setInt(1, customerID);
			s.setString(2, date);

			ResultSet rs = s.executeQuery();
			int ret = -1;
			while (rs.next()) {
				ret = rs.getInt("billID");
			}
			rs.close();
			debug.debug("Got billID for customer: "+ customerID + " date: " + date);
			return ret;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * Gets a specific property.
	 * 
	 * @param prop Property which should be fetched
	 * @return value of the property | null if the property isn't existing
	 */
	public static String getProperty(Property prop) {
		String sql = "SELECT value FROM properties WHERE key = ?;";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(sql);) {
			s.setString(1, prop.toString());

			ResultSet rs = s.executeQuery();
			String res = rs.getString("value");
			rs.close();

			debug.debug("Got property " + prop.toString());
			return res;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Returns the LaTeX code for the summary of all purchases.
	 * 
	 * @param purchaseIDs
	 * @param groupProducts Group the products by productID?
	 * @return
	 */
	public static String getTexTotalPurchase(List<Integer> purchaseIDs, boolean groupProducts) {
		String tableEnd = FileHandler.readFromFile(Configuration.TEXTABLEEND);
		if (!purchaseIDs.isEmpty()) {
			String str = "SELECT sum(quantity) as total, sum(quantity*salePrice) as sum, name, prod.productID, salePrice from product prod, purchasecontainsproduct pcp, purchase p WHERE prod.productID = pcp.productID AND pcp.purchaseID = p.purchaseID ";
			String str2 = "SELECT quantity as total, quantity*salePrice as sum, name, prod.productID, salePrice from product prod, purchasecontainsproduct pcp, purchase p WHERE prod.productID = pcp.productID AND pcp.purchaseID = p.purchaseID ";

			try (Connection conn = DatabaseHandler.getConnection();) {

				StringBuilder sb = new StringBuilder();
				if (groupProducts) {
					sb.append(str);
				} else {
					sb.append(str2);
				}

				if (!purchaseIDs.isEmpty()) {
					sb.append(" AND (");
					for (Integer i : purchaseIDs) {
						String str3 = " p.purchaseID = " + i + " OR ";
						sb.append(str3);
					}
					sb.delete(sb.length() - 4, sb.length() - 1);
					sb.append(")");
				}
				if (groupProducts) {
					sb.append(" GROUP BY prod.productID ORDER BY name ASC;");
				} else {
					sb.append("ORDER BY name ASC;");
				}

				PreparedStatement s = conn.prepareStatement(sb.toString());
				ResultSet rs = s.executeQuery();

				StringBuilder sb2 = new StringBuilder();
				double totalSum = 0.0;

				while (rs.next()) {

					totalSum += rs.getDouble("sum");

					sb2.append(rs.getString("name"));
					sb2.append("&");
					sb2.append(rs.getInt("total"));
					sb2.append("&");
					sb2.append(InvoiceHandler.formatValueToInvoice(rs.getDouble("salePrice")));
					sb2.append("\\euro &");
					sb2.append(InvoiceHandler.formatValueToInvoice(rs.getDouble("sum")));
					sb2.append("\\euro");
					sb2.append("\\\\");
				}
				sb2.append(tableEnd.replaceFirst("total", InvoiceHandler.formatValueToInvoice(totalSum)));

				return sb2.toString();
			} catch (SQLException e) {
				log.error(e.getMessage());
				e.printStackTrace();
			}
		}
		return tableEnd;
	}

	/**
	 * Gets the date of an purchase by the given purchaseID
	 * 
	 * @param purchase
	 * @return
	 */
	public static String getSalesday(int purchaseID) {
		String query = "SELECT date FROM purchase p, salesday s WHERE p.salesday = s.salesdayID AND purchaseID = ?;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(query);) {
			prep.setInt(1, purchaseID);

			ResultSet rs = prep.executeQuery();
			String res = rs.getString("date");
			rs.close();

			return res;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Returns a Map containing the Staff and the number of the days this staff
	 * has been working.
	 * 
	 * @param firstDay of work
	 * @param lastDay of work
	 * @return map | null if an error occurred
	 */
	public static Map<Staff, Integer> getStaffWorking(Date firstDay, Date lastDay) {
		Map<Staff, Integer> resMap = new HashMap<>();

		String str = "SELECT count(salesdayID) as count, staff.staffID from salesday s, person p, staff where date between ? and ? AND s.staffID = staff.staffID AND staff.person = personID GROUP by staff.staffID;";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(str);) {
			prep.setString(1, DateHandler.formatNormalDate(firstDay));
			prep.setString(2, DateHandler.formatNormalDate(lastDay));

			ResultSet rs = prep.executeQuery();
			while (rs.next()) {
				Staff staff = TableControl.getInstance().getStaffMap().get(rs.getInt("staffID"));
				int count = rs.getInt("count");

				resMap.put(staff, count);
			}
			rs.close();
			return resMap;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Returns the profit that was made with the given purchaseIDs
	 * 
	 * @param purchaseIDs
	 * @return profit made with the purchaseIDs | -1000000000 if an error
	 *         occurred
	 */
	public static double getProfit(List<Integer> purchaseIDs) {
		String query = "SELECT sum(quantity) as total, sum(quantity*(salePrice-cost)) as profit, name, prod.productID, (salePrice-cost) as marge from product prod, purchasecontainsproduct pcp, purchase p WHERE prod.productID = pcp.productID AND pcp.purchaseID = p.purchaseID ";

		try (Connection conn = DatabaseHandler.getConnection();) {

			StringBuilder sb = new StringBuilder();
			sb.append(query);

			if (!purchaseIDs.isEmpty()) {
				sb.append(" AND (");
				for (Integer i : purchaseIDs) {
					String str3 = " p.purchaseID = " + i + " OR ";
					sb.append(str3);
				}
				sb.delete(sb.length() - 4, sb.length() - 1);
				sb.append(")");
			}
			sb.append(" GROUP BY prod.productID;");

			PreparedStatement s = conn.prepareStatement(sb.toString());
			ResultSet rs = s.executeQuery();

			double totalSum = 0.0;
			while (rs.next()) {

				totalSum += rs.getDouble("profit");
			}
			return totalSum;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return -1000000000;
	}

	/**
	 * @param date in the format yyyy-MM
	 * @return the total profit of the given month
	 */
	public static double getTotalProfit(Date date) {
		String stringDate = DateHandler.formatMonthDateToString(date);

		String query = "SELECT sum(profit) as totalProfit FROM "
				+ "(SELECT sum(quantity*(salePrice-cost)) as profit from product prod, purchasecontainsproduct pcp, purchase p , bill WHERE bill.billID = p.billID AND date = ? AND prod.productID = pcp.productID AND pcp.purchaseID = p.purchaseID GROUP BY prod.productID);";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(query)) {
			prep.setString(1, stringDate);

			ResultSet rs = prep.executeQuery();

			double totalProfit = rs.getDouble("totalProfit");
			return totalProfit;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * Return the total sum of bill with the given billID
	 * 
	 * @param billID
	 * @return
	 */
	public static double getTotalSumOfBill(int billID) {

		String query = "SELECT sum(quantity*salePrice) as amount FROM "
				+ "(SELECT * FROM bill b, product p, purchase pu, purchasecontainsproduct pcp WHERE b.billID = ? AND pu.billID = b.billID AND pu.purchaseID = pcp.purchaseID AND pcp.productID = p.productID);";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement prep = conn.prepareStatement(query)) {
			prep.setInt(1, billID);

			ResultSet rs = prep.executeQuery();

			double amount = rs.getDouble("amount");
			return amount;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * Returns the LaTeX code of the total profit by the given purchaseIDs
	 * 
	 * @param purchaseIDs
	 * @return
	 */
	public static String getTexProfit(List<Integer> purchaseIDs) {

		String tableEnd = FileHandler.readFromFile(Configuration.TEXTABLEPROFITEND);
		if (!purchaseIDs.isEmpty()) {
			String str = "SELECT sum(quantity) as total, sum(quantity*(salePrice-cost)) as profit, name, prod.productID, (salePrice-cost) as marge from product prod, purchasecontainsproduct pcp, purchase p WHERE prod.productID = pcp.productID AND pcp.purchaseID = p.purchaseID";

			try (Connection conn = DatabaseHandler.getConnection();) {

				StringBuilder sb = new StringBuilder();
				sb.append(str);

				if (!purchaseIDs.isEmpty()) {
					sb.append(" AND (");
					for (Integer i : purchaseIDs) {
						String str3 = " p.purchaseID = " + i + " OR ";
						sb.append(str3);
					}
					sb.delete(sb.length() - 4, sb.length() - 1);
					sb.append(")");
				}
				sb.append(" GROUP BY prod.productID ORDER BY name ASC;");

				PreparedStatement s = conn.prepareStatement(sb.toString());
				ResultSet rs = s.executeQuery();

				StringBuilder sb2 = new StringBuilder();

				while (rs.next()) {

					sb2.append(rs.getString("name"));
					sb2.append("&");
					sb2.append(rs.getInt("total"));
					sb2.append("&");
					sb2.append(InvoiceHandler.formatValueToInvoice(rs.getDouble("marge")));
					sb2.append("\\euro &");
					sb2.append(InvoiceHandler.formatValueToInvoice(rs.getDouble("profit")));
					sb2.append("\\euro");
					sb2.append("\\\\");
				}

				double totalProfit = getTotalProfit(DateHandler.getMonthBeforeDate(new Date()));
				double totalSalary = -getTotalSalary(DateHandler.getMonthBeforeDate(new Date()));

				double beforeProfit = totalProfit + totalSalary;
				tableEnd = tableEnd.replaceFirst("profit", InvoiceHandler.formatValueToInvoice(totalProfit));

				tableEnd = tableEnd.replaceFirst("staffcost", InvoiceHandler.formatValueToInvoice(totalSalary));
				tableEnd = tableEnd.replaceFirst("beforeProfit", InvoiceHandler.formatValueToInvoice(beforeProfit));

				sb2.append(tableEnd);

				return sb2.toString();
			} catch (SQLException e) {
				log.error(e.getMessage());
				e.printStackTrace();
			}
		}
		return tableEnd;
	}

	/**
	 * Initializes bill with the given billID
	 * 
	 * @param billID
	 * @return
	 * @throws SQLException 
	 */
	public static Bill initializeBill(int billID) throws SQLException {

		String str = "SELECT * FROM bill WHERE billID = ? AND status = " + Status.TOCALCULATE.toInt() + ";";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str)) {
			s.setInt(1, billID);

			ResultSet rs = s.executeQuery();
			File file = new File(Configuration.PATHTEXINVOICECUSTOMER + billID + rs.getString("date") + ".pdf");
			file.deleteOnExit();
			if (rs.next()) {
				byte[] arr = rs.getBytes("pdf");

				if (arr == null) {
					file = null;
				} else {
					FileOutputStream fos = new FileOutputStream(file);
					fos.write(arr);
					fos.close();
				}
			}

			Bill bill = Bill.createExistingBill(rs.getInt("billID"), file, rs.getString("date"), Status.fromInt(rs.getInt("status")), TableControl
					.getInstance().getCustomerMap().get(rs.getInt("customerID")), rs.getDouble("amount"));
			rs.close();
			return bill;
		} catch (IOException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Fetches product data from the database and initializes the products.
	 */
	public static void initializeProducts() {
		String sql = "SELECT * FROM product WHERE status = ?;";
		IDualMap<Integer, String, Product> productMap = TableControl.getInstance().getProductMap();
		Map<Product, List<Integer>> prodMap = new HashMap<>();

		try (Connection conn = DatabaseHandler.getConnection(); ) {
			PreparedStatement s = conn.prepareStatement(sql);
			s.setInt(1, Status.ACTIVE.toInt());
			ResultSet rs = s.executeQuery();

			while (rs.next()){

				Product prod = Product.createExistingProduct(rs.getInt("productID"), rs.getString("name"), rs.getDouble("cost"), rs.getInt("availableBegin"),
						rs.getInt("availableEnd"), rs.getDouble("salePrice"), null, Status.fromInt(rs.getInt("status")), rs.getInt("stock"),
						rs.getInt("multiplicator"), BoxType.fromString(rs.getString("category")), null, rs.getInt("maxStock"), rs.getInt("trayCount"), rs.getInt("groupID"));
				
				List<Integer> list = new ArrayList<>();
				list.add(0, rs.getInt("hasDeposit"));
				list.add(1, rs.getInt("hasReplacement"));
				prodMap.put(prod, list);
				productMap.put(prod.getID(), prod.getName(), prod);
				
				debug.info("Product with ID: " + prod.getID() + " has been initialized.");
			}
			rs.close();
			
			setDepositAndReplacementProducts(productMap, prodMap);
			debug.info("All products successfully initialized");
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
	}

	private static void setDepositAndReplacementProducts(IDualMap<Integer, String, Product> productMap, Map<Product, List<Integer>> prodMap) {
		for(Product p: prodMap.keySet()){
			List<Integer> depositAndReplacmentProds = prodMap.get(p);
			if(depositAndReplacmentProds.get(0) != 0){
				p.setDeposit(productMap.get(depositAndReplacmentProds.get(0)));
			}
			if(depositAndReplacmentProds.get(1) != 0){
				p.setReplacement(createProduct(depositAndReplacmentProds.get(1)));
			}
		}
	}
	
	private static Product createProduct(int productID) {
		String query = "SELECT * FROM product WHERE productID = ?;";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {
			
			s.setInt(1, productID);
			ResultSet rs = s.executeQuery();
			
			Product prod = Product.createExistingProduct(rs.getInt("productID"), rs.getString("name"), rs.getDouble("cost"), rs.getInt("availableBegin"),
					rs.getInt("availableEnd"), rs.getDouble("salePrice"), TableControl.getInstance().getProductMap().get(rs.getInt("hasDeposit")), Status.fromInt(rs.getInt("status")), rs.getInt("stock"),
					rs.getInt("multiplicator"), BoxType.fromString(rs.getString("category")), TableControl.getInstance().getProductMap().get(rs.getInt("hasReplacement")), rs.getInt("maxStock"), rs.getInt("trayCount"), rs.getInt("groupID"));
//			rs.close();
			
			return prod;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return null;
	}

	public static List<Product> getProductGroup(int groupID) {
		String query = "SELECT * FROM product WHERE groupID = ? AND status = ? ORDER BY cost ASC;";
		List<Product> prodList = new ArrayList<Product>();
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {

			s.setInt(1, groupID);
			s.setInt(2, Status.WAIT.toInt());
			ResultSet rs = s.executeQuery();

			IDualMap<Integer, String, Product> productMap = TableControl.getInstance().getProductMap();
			Map<Product, List<Integer>> prodMap = new HashMap<>();
			while (rs.next()){

				Product prod = Product.createExistingProduct(rs.getInt("productID"), rs.getString("name"), rs.getDouble("cost"), rs.getInt("availableBegin"),
						rs.getInt("availableEnd"), rs.getDouble("salePrice"), null, Status.fromInt(rs.getInt("status")), rs.getInt("stock"),
						rs.getInt("multiplicator"), BoxType.fromString(rs.getString("category")), null, rs.getInt("maxStock"), rs.getInt("trayCount"), rs.getInt("groupID"));
				
				List<Integer> list = new ArrayList<>();
				list.add(0, rs.getInt("hasDeposit"));
				list.add(1, rs.getInt("hasReplacement"));
				prodMap.put(prod, list);
			}
			
			for(Product p: prodMap.keySet()){
				List<Integer> depositAndReplacmentProds = prodMap.get(p);
				if(depositAndReplacmentProds.get(0) != 0){
					p.setDeposit(productMap.get(depositAndReplacmentProds.get(0)));
				}else if(depositAndReplacmentProds.get(1) != 0){
					p.setReplacement(productMap.get(depositAndReplacmentProds.get(0)));
				}
				prodList.add(p);
			}
			rs.close();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}	
		return prodList;
	}

	/**
	 * Gets all staff from the database and creates <code>Staff</code> objects.
	 * These objects are stored at <code>TableControl</code> - the main control
	 * for interacting with initialized data.
	 */
	public static void initializeStaff() {
		debug.info("intialize staff...");
		try (Connection conn = DatabaseHandler.getConnection(); Statement s = conn.createStatement();) {

			ResultSet rs = s.executeQuery("SELECT * FROM staff, person WHERE staffStatus != 0 AND personStatus = 1 AND person=personID;");
			while (rs.next()) {

				Person p = Person.createExistingPerson(rs.getInt("personID"), rs.getString("firstName"), rs.getString("lastName"), rs.getInt("room"),
						Status.fromInt(rs.getInt("personStatus")), rs.getString("email"));
				Staff staff = Staff.createExistingStaff(rs.getInt("staffID"), p, rs.getString("password"), rs.getString("salt"),
						Status.fromInt(rs.getInt("staffStatus")), rs.getString("nickname"));
				TableControl.getInstance().getStaffMap().put(rs.getInt("staffID"), rs.getString("nickname"), staff);
				debug.info("Staff initialized nick: " + staff.getNickname());
			}
			rs.close();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
	}

	/**
	 * Fetches all customer data from the database and initializes the active
	 * ones.
	 * 
	 * @return
	 */
	public static List<Customer> initializeCustomer() {
		List<Customer> customerList = new ArrayList<>();
		debug.info("initialize customer...");
		String sql = "select person.*, customer.*, billID, bill.date from person,customer left outer join bill on bill.customerID = customer.customerID AND date = strftime('%Y-%m', 'now', '-1 month') WHERE customer.person = person.personID AND customerStatus = ? AND personStatus = ?;";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(sql);) {
			s.setInt(1, Status.ACTIVE.toInt());
			s.setInt(2, Status.ACTIVE.toInt());

			ResultSet rs = s.executeQuery();
			while (rs.next()) {

				int customerID = rs.getInt("customerID");

				Customer customer = Customer.createExisting(customerID, rs.getString("name"), PaymentType.fromInt(rs.getInt("payment")),
						Status.ACTIVE, rs.getInt("billID"), Person.createExistingPerson(rs.getInt("personID"), rs.getString("firstName"),
								rs.getString("lastName"), rs.getInt("room"), Status.fromInt(rs.getInt("personStatus")), rs.getString("email")));

				customerList.add(customer);

				debug.info("Customer initialized name: " + rs.getString("name"));
			}
			rs.close();
			return customerList;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}

		return customerList;
	}

	/**
	 * Writes a bill to the database
	 * 
	 * @param billID
	 * @param pdf
	 * @param date
	 * @param customerID
	 * @param status
	 */
	public static boolean writeBillToDB(boolean hasChanged, int billID, File pdf, String date, int customerID, Status status, double amount) {

		String str = "INSERT INTO bill(billID, pdf, date, customerID, status, amount) VALUES (?,?,?,?,?,?);";
		String update = "UPDATE bill SET billID = ?, pdf = ?, date = ?, customerID = ?, status = ?, amount = ? WHERE billID = ?";

		String query;
		if (hasChanged) {
			query = update;
		} else {
			query = str;
		}

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {
			FileInputStream fis = null;
			int invoiceFileLength = 0;
			if (pdf != null) {
				fis = new FileInputStream(pdf);
				invoiceFileLength = (int) pdf.length();
				s.setBinaryStream(2, fis, invoiceFileLength);
			} else {
				s.setNull(2, java.sql.Types.BLOB);
			}
			s.setInt(1, billID);

			s.setString(3, date);
			s.setInt(4, customerID);
			s.setInt(5, status.toInt());
			s.setDouble(6, amount);

			if (hasChanged) {
				s.setInt(7, billID);
			}
			s.executeUpdate();

			return true;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e1) {
			log.error(e1.getMessage());
			e1.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a <code>Purchase</code> to the database.
	 * 
	 * @param billID
	 * @param isPaid
	 * @param salesdayID
	 * @param customerName
	 * @param status
	 * @return
	 */
	public static boolean writePurchaseToDB(final int billID, final int purchaseID, final boolean isPaid, final int salesdayID, final Status status) {
		try (Connection conn = DatabaseHandler.getConnection();
				PreparedStatement s = conn.prepareStatement("INSERT INTO purchase(purchaseID, paid, salesday, status, billID) VALUES (?, ?, ?,?,?);");) {

			s.setInt(1, purchaseID);
			s.setBoolean(2, isPaid);
			s.setInt(3, salesdayID);
			s.setInt(4, status.toInt());
			s.setInt(5, billID);
			s.executeUpdate();
			debug.info("Purchase written to DB ID: " + purchaseID + " paid: " + isPaid);

			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes <code>BillContainsProduct</code> to the database.
	 * 
	 * @param quantity
	 * @param billContainsProduct
	 * @return
	 */
	public static boolean writePurchaseContainsProductToDB(final int pcpID, final int purchaseID, final int productID, final int quantity,
			final Status status) {
		try (Connection conn = DatabaseHandler.getConnection();
				PreparedStatement s = conn
						.prepareStatement("INSERT INTO purchasecontainsproduct(purchaseID,productID,quantity,status, pcpID) VALUES (?, ?, ?,?,?);");) {

			s.setInt(1, purchaseID);
			s.setInt(2, productID);
			s.setInt(3, quantity);
			s.setInt(4, status.toInt());
			s.setInt(5, pcpID);
			s.executeUpdate();
			debug.info("BillContainsProduct written to DB. purchaseID: " + purchaseID + " productID: " + productID);

			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a product to the database.
	 * <p>
	 * It is just written to the database if it has changed or if it is a new
	 * <code>Product</code>.
	 * 
	 * @param hasChanged have the values of the product changed? true => the row
	 *            gets updated | false => a new row is inserted
	 * @param productID
	 * @param name
	 * @param cost
	 * @param availableBegin
	 * @param availableEnd
	 * @param salePrice
	 * @param deposit
	 * @param status
	 * @param category
	 * @param stock
	 * @param maxStock
	 * @param trayCount
	 * @param groupID 
	 * @return
	 */
	public static boolean writeProductToDB(final boolean hasChanged, final int productID, final String name, final double cost,
			final int availableBegin, final int availableEnd, final double salePrice, final Product deposit, final Status status, BoxType category,
			int stock, Product hasReplacement, int multiplicator, int maxStock, int trayCount, int groupID) {

		assert name != null;

		String changedSQL = "UPDATE product SET productID = ?, name = ?, cost = ?, availableBegin = ?, availableEnd = ? "
				+ ", salePrice = ?, hasDeposit = ?, status = ?, category = ?, stock = ?, multiplicator =  ?, hasReplacement = ?, maxStock = ?, trayCount = ?, groupID = ? WHERE productID = ?;";
		String insertSQL = "INSERT INTO product(productID,name,cost,availableBegin,availableEnd,salePrice,hasDeposit,status,category,stock, multiplicator, hasReplacement, maxStock, trayCount, groupID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement s = null;
			if (hasChanged) {
				s = conn.prepareStatement(changedSQL);

				s.setInt(16, productID);

				debug.info("Product was changed ID: " + productID);
			} else {

				s = conn.prepareStatement(insertSQL);

				debug.info("New product created ID: " + productID);
			}
			s.setInt(1, productID);
			s.setString(2, name);
			s.setDouble(3, cost);
			s.setInt(4, availableBegin);
			s.setInt(5, availableEnd);
			s.setDouble(6, salePrice);
			if (deposit == null) {
				s.setNull(7, java.sql.Types.INTEGER);
			} else {
				s.setInt(7, deposit.getID());
			}
			s.setInt(8, status.toInt());
			s.setString(9, category.toString());
			s.setInt(10, stock);
			s.setInt(11, multiplicator);
			if (hasReplacement == null) {
				s.setNull(12, java.sql.Types.INTEGER);
			} else {
				s.setInt(12, hasReplacement.getID());
			}
			s.setInt(13, maxStock);
			s.setInt(14, trayCount);
			s.setInt(15, groupID);

			s.executeUpdate();
			s.close();

			return true;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a person to the database.
	 * 
	 * @param hasChanged have the values of the person changed? true => the row
	 *            gets updated | false => a new row is inserted
	 * @param personID
	 * @param firstName
	 * @param lastName
	 * @param room
	 * @param status
	 * @param email
	 * @return
	 */
	public static boolean writePersonToDB(boolean hasChanged, int personID, String firstName, String lastName, int room, Status status, String email) {
		try (Connection conn = DatabaseHandler.getConnection();) {

			String changedSQL = "UPDATE person SET personID = ?,firstName = ?,lastName = ?,room=  ?, personStatus = ?, email = ? WHERE personID = ?;";
			String insertSQL = "INSERT INTO person(personID,firstName,lastName,room, personStatus, email) VALUES (?,?,?,?,?,?);";

			PreparedStatement s = null;

			if (hasChanged) {
				s = conn.prepareStatement(changedSQL);
				s.setInt(7, personID);

				debug.info("Person was changed ID: " + personID);
			} else {

				s = conn.prepareStatement(insertSQL);
				debug.info("New person created ID: " + personID);
			}
			s.setInt(1, personID);
			s.setString(2, firstName);
			s.setString(3, lastName);
			s.setInt(4, room);
			s.setInt(5, status.toInt());
			s.setString(6, email);
			s.executeUpdate();
			s.close();

			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a staff to the database.
	 * 
	 * @param hasChanged have the values of the staff changed? true => the row
	 *            gets updated | false => a new row is inserted
	 * @param nickname
	 * @param password
	 * @param salt
	 * @param personID
	 * @param status
	 */
	public static boolean writeStaffToDB(boolean hasChanged, String nickname, String password, String salt, int personID, Status status, int staffID) {
		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement s = null;
			String update = "UPDATE staff SET nickname = ?, password = ? ,salt = ?, person = ?,staffStatus = ? ,staffID = ? WHERE staffID = ?;";
			String insert = "INSERT INTO staff (nickname,password,salt, person,staffStatus,staffID) VALUES (?,?,?,?,?,?);";

			if (hasChanged) {

				s = conn.prepareStatement(update);
				s.setInt(7, staffID);
				debug.info("Staff has been updated ID: " + nickname);
			} else {
				s = conn.prepareStatement(insert);
				debug.info("Staff has been created ID: " + nickname);
			}
			s.setString(1, nickname);
			s.setString(2, password);
			s.setString(3, salt);
			s.setInt(4, personID);
			s.setInt(5, status.toInt());
			s.setInt(6, staffID);
			s.executeUpdate();
			s.close();

			return true;

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Changes the value of an property
	 * 
	 * @param key
	 * @param value
	 */
	public static void writeToProperty(Property key, String value) {
		String str = "UPDATE properties SET value = ? WHERE key = ?;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(str);) {
			s.setString(1, value);
			s.setString(2, key.toString());
			s.executeUpdate();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
	}

	/**
	 * Writes a created invoice of a customer to the database.
	 * 
	 * @param billID
	 * @param invoice
	 * @param status
	 */
	public static void writeInvoiceToDatabase(int id, String date, File salaryInvoice, File balanceInvoice) {
		String query = " INSERT INTO invoice(invoiceID, date, salaryInvoice, balanceInvoice) VALUES (?,?, ?, ?);";

		try (Connection conn = DatabaseHandler.getConnection();
				PreparedStatement prep = conn.prepareStatement(query);
				FileInputStream fisSalary = new FileInputStream(salaryInvoice);
				FileInputStream fisBalance = new FileInputStream(balanceInvoice);) {

			prep.setInt(1, id);
			prep.setString(2, date);

			int salaryFileLength = (int) salaryInvoice.length();
			prep.setBinaryStream(3, fisSalary, salaryFileLength);

			int balanceFileLength = (int) balanceInvoice.length();
			prep.setBinaryStream(4, fisBalance, balanceFileLength);

			prep.executeUpdate();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e1) {
			log.error(e1.getMessage());
			e1.printStackTrace();
		}
	}

	/**
	 * Writes a created invoice of a customer to the database.
	 * 
	 * @param billID
	 * @param invoice
	 * @param status
	 * @param amount
	 */
	public static boolean writeInvoiceCustomerToDatabase(int billID, File invoice, Status status, double amount) {
		String query = "UPDATE bill SET pdf = ?, status = ?, amount = ? WHERE billID = ?;";

		try (Connection conn = DatabaseHandler.getConnection();
				PreparedStatement prep = conn.prepareStatement(query);
				FileInputStream fis = new FileInputStream(invoice);) {
			int fileLength = (int) invoice.length();
			prep.setBinaryStream(1, fis, fileLength);
			prep.setInt(2, status.toInt());
			prep.setDouble(3, amount);
			prep.setInt(4, billID);

			prep.executeUpdate();

			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e1) {
			log.error(e1.getMessage());
			e1.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a customer to the database.
	 * 
	 * @param hasChanged
	 * @param customerID
	 * @param name
	 * @param payment
	 * @param personID
	 * @param status
	 * @return
	 */
	public static boolean writeCustomerToDB(boolean hasChanged, int customerID, String name, PaymentType payment, int personID, Status status) {
		String update = "UPDATE customer SET customerID = ?, name = ?, payment = ?, person= ?, customerStatus = ? WHERE customerID = ?;";
		String insert = "INSERT INTO customer(customerID, name, payment, person, customerStatus) VALUES (?,?,?,?,?);";
		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement prep = null;
			if (hasChanged) {
				prep = conn.prepareStatement(update);

				prep.setInt(6, customerID);
			} else {
				prep = conn.prepareStatement(insert);
			}
			prep.setInt(1, customerID);
			prep.setString(2, name);
			prep.setInt(3, payment.toInt());
			prep.setInt(4, personID);
			prep.setInt(5, status.toInt());
			prep.executeUpdate();
			prep.close();
			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Writes a salesday to the database.
	 * 
	 * @param hasChanged have the values of the salesday changed? true => the
	 *            row gets updated | false => a new row is inserted
	 * @param salesdayID ID of the salesday
	 * @param date
	 * @param staffID
	 * @param status
	 * @param cashBegin
	 * @param cashEnd
	 * @param coinBegin
	 * @param coinEnd
	 * @return
	 */
	public static boolean writeSalesdayToDB(final boolean hasChanged, final int salesdayID, final String date, final int staffID,
			final Status status, final double cashBegin, final double cashEnd, final int coinBegin, final int coinEnd) {
		assert date != null;

		String insert = "INSERT INTO salesday(salesdayID, date, staffID, status, cashBegin, coinBegin, cashEnd, coinEnd) VALUES (?, ?, ?,?,?,?,?,?);";
		String update = "UPDATE salesday SET cashEnd = ?, coinEnd = ? WHERE salesdayID = ?;";

		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement s = null;

			if (hasChanged) {
				s = conn.prepareStatement(update);
				s.setDouble(1, cashEnd);
				s.setInt(2, coinEnd);
				s.setInt(3, salesdayID);
			} else {
				s = conn.prepareStatement(insert);
				s.setInt(1, salesdayID);
				s.setString(2, date);
				s.setInt(3, staffID);
				s.setInt(4, status.toInt());
				s.setDouble(5, cashBegin);
				s.setInt(6, coinBegin);
				s.setDouble(7, cashEnd);
				s.setInt(8, coinEnd);
			}
			s.executeUpdate();
			s.close();
			debug.info("Salesday written to DB ID: " + salesdayID + " changed:" + hasChanged);

			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

//	private static Product createProduct(Statement s, ResultSet rs) throws SQLException {
//
//		Product deposit = null;
//		if (rs.getInt("hasDeposit") != 0) {
//			deposit = TableControl.getInstance().getProductMap().get(rs.getInt("hasDeposit"));
//		}
//		int hasReplacement = rs.getInt("hasReplacement");
//		Product replacement = null;
//		if (hasReplacement != 0) {
//			ResultSet allProducts = s.executeQuery("SELECT * FROM product WHERE productID = " + hasReplacement + ";");
//			replacement = createProduct(s, allProducts);
//		}
//
//		Product prod = Product.createExistingProduct(rs.getInt("productID"), rs.getString("name"), rs.getDouble("cost"), rs.getInt("availableBegin"),
//				rs.getInt("availableEnd"), rs.getDouble("salePrice"), deposit, Status.fromInt(rs.getInt("status")), rs.getInt("stock"),
//				rs.getInt("multiplicator"), BoxType.fromString(rs.getString("category")), replacement, rs.getInt("maxStock"), rs.getInt("trayCount"), rs.getInt("groupID"));
//
//		return prod;
//	}

	/**
	 * Get an active debt of a customer. Set date = null if one wants all active
	 * debts of the customer otherwise sepcify the date of the active debt you
	 * would like to know.
	 * 
	 * @param c
	 * @param date
	 * @return
	 */
	public static Bill getActiveDebt(Customer c, Date date) {
		Bill bill = null;
		String queryWithoutDate = "SELECT * from bill where customerID = ? AND status = ?";
		String query = "SELECT * from bill where date = ? AND customerID = ? AND status = ?";

		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement prep = null;
			if (date == null) {
				prep = conn.prepareStatement(queryWithoutDate);
				prep.setInt(1, c.getCustomerID());
				prep.setInt(2, Status.ACTIVE.toInt());
			} else {
				prep = conn.prepareStatement(query);
				prep.setString(1, DateHandler.formatMonthDateToString(date));
				prep.setInt(2, c.getCustomerID());
				prep.setInt(3, Status.ACTIVE.toInt());
			}

			ResultSet rs = prep.executeQuery();
			if (!rs.isAfterLast()) {
				File file = new File(Configuration.PATHTEXINVOICECUSTOMER + c.getCustomerID() + "_" + rs.getString("date") + ".pdf");
				file.deleteOnExit();
				if (rs.next()) {
					byte[] arr = rs.getBytes("pdf");

					FileOutputStream fos = new FileOutputStream(file);
					if (arr == null) {
						file = null;
					} else {
						fos.write(arr);
					}
					fos.close();
				}
				if (rs.isAfterLast()) {
					return null;
				} else {
					bill = Bill.createExistingBill(rs.getInt("billID"), file, rs.getString("date"), Status.ACTIVE, c, rs.getDouble("amount"));
				}
			}
			prep.close();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return bill;
	}

	/**
	 * @return a List of all bills saved in the database; The bill objects don't
	 *         contain the file because it's too expensive reading all .pdfs
	 *         from the database
	 */
	public static List<Bill> getHistoryBills() {
		List<Bill> billList = new ArrayList<>();
		String query = "SELECT * FROM bill WHERE pdf NOT NULL;";
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {
			ResultSet rs = s.executeQuery();

			while (rs.next()) {
				if (!rs.isAfterLast()) {
					billList.add(Bill.createExistingBill(rs.getInt("billID"), null, rs.getString("date"), Status.fromInt(rs.getInt("status")),
							TableControl.getInstance().getCustomerMap().get(rs.getInt("customerID")), rs.getDouble("amount")));
				}
			}

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return billList;
	}

	/**
	 * Returns a List containing all active persons.
	 * 
	 * @return
	 */
	public static List<Person> getPersons() {
		List<Person> personList = new ArrayList<>();
		String query = "SELECT * FROM person WHERE personStatus = ?;";

		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {
			s.setInt(1, Status.ACTIVE.toInt());

			ResultSet rs = s.executeQuery();

			while (rs.next()) {
				personList.add(Person.createExistingPerson(rs.getInt("personID"), rs.getString("firstName"), rs.getString("lastName"),
						rs.getInt("room"), Status.fromInt(rs.getInt("personStatus")), rs.getString("email")));
			}

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return personList;
	}

	public static Person getPerson(int personID) {
		String query = "SELECT * FROM person WHERE personID = ?;";
		Person p = null;
		try (Connection conn = DatabaseHandler.getConnection(); PreparedStatement s = conn.prepareStatement(query);) {
			s.setInt(1, personID);

			ResultSet rs = s.executeQuery();

			p = Person.createExistingPerson(personID, rs.getString("firstName"), rs.getString("lastName"), rs.getInt("room"),
					Status.fromInt(rs.getInt("status")), rs.getString("email"));
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return p;
	}

	/**
	 * Writes an inventory to the database/updates an inventory in the database.
	 * Insert or update indicated by boolean flag hasChanged
	 * 
	 * @param hasChanged
	 * @param inventoryID
	 * @param date
	 * @param comment
	 * @param status
	 * @param file
	 * @return
	 */
	public static boolean writeInventoryToDB(boolean hasChanged, int inventoryID, String date, String comment, Status status, File file) {
		String update = "UPDATE inventory SET date = ?, comment = ?, pdf = ?, status = ? WHERE inventoryID = ?";
		String insert = "INSERT INTO inventory(date, comment, pdf, status, inventoryID) VALUES (?,?,?,?,?)";

		try (Connection conn = DatabaseHandler.getConnection();) {
			PreparedStatement prep = null;
			if (hasChanged) {
				prep = conn.prepareStatement(update);
			} else {
				prep = conn.prepareStatement(insert);
			}
			prep.setString(1, date);
			prep.setString(2, comment);

			if (file != null) {
				try (FileInputStream fis = new FileInputStream(file);) {
					int length = (int) file.length();
					prep.setBinaryStream(3, fis, length);
				} catch (IOException e) {
					log.error(e.getMessage());
					e.printStackTrace();
				}
			} else {
				prep.setNull(3, java.sql.Types.BLOB);
			}
			prep.setInt(4, status.toInt());
			prep.setInt(5, inventoryID);

			prep.executeUpdate();

			debug.info("Inventory written to database");
			return true;
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	public static Map<String, Number> getRevenueData(boolean isRevenue, List<IFinishable> selectedCustomer, LocalDate begin, LocalDate end) {
		String revenueQuery = "SELECT sum(prod.salePrice*pcp.quantity) as revenue, day.date as mydate FROM bill b, customer c, "
				+ "salesday day, product prod, purchase purch, purchasecontainsproduct pcp WHERE pcp.purchaseID = purch.purchaseID "
				+ "AND purch.salesday = day.salesdayID AND pcp.productID = prod.productID AND purch.billID = b.billID AND b.customerID = c.customerID AND mydate between ? and ? ";
		
		String profitQuery = "SELECT sum(prod.salePrice*pcp.quantity - prod.cost*pcp.quantity) as revenue, day.date as mydate FROM bill b, customer c, "
				+ "salesday day, product prod, purchase purch, purchasecontainsproduct pcp WHERE pcp.purchaseID = purch.purchaseID "
				+ "AND purch.salesday = day.salesdayID AND pcp.productID = prod.productID AND purch.billID = b.billID AND b.customerID = c.customerID AND mydate between ? and ? "; 
				
		StringBuilder sb = new StringBuilder();
		if(isRevenue){
			sb.append(revenueQuery);			
		} else{
			sb.append(profitQuery);
		}
		if(!selectedCustomer.isEmpty()){
			for(IFinishable c: selectedCustomer){
				sb.append("AND c.customerID = " + ((Customer) c).getCustomerID()+" ");
			}
		}
		sb.append("GROUP BY mydate ORDER BY mydate COLLATE NOCASE ASC;");
		Map<String, Number> resMap = new LinkedHashMap<>();
		try(Connection conn = DatabaseHandler.getConnection()){
			PreparedStatement prep = conn.prepareStatement(sb.toString());
			prep.setString(1, begin.toString());
			prep.setString(2, end.toString());
			ResultSet rs = prep.executeQuery();
			
			
			while(rs.next()){
				resMap.put(rs.getString("mydate"), rs.getDouble("revenue"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
		return resMap;
	}


}
